package yu.zhou.njupt.importdataui.core.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.google.common.base.Joiner;

import yu.zhou.njupt.importdataui.core.Importer;
import yu.zhou.njupt.importdataui.entity.Column;
import yu.zhou.njupt.importdataui.entity.ImportResult;
import yu.zhou.njupt.importdataui.util.RegexUtil;

/** 
 * 用于导入xlsx文件，
 * 文件名以"$-"开始，一个文件对应一张表，文件名为表名，文件里的多个sheet全是同一张表的数据。
 * 文件名不以"$-"，文件里的每个sheet对应一张表，sheet名为表名。
**/
public class XlsxImporter extends Importer{
    
    public XlsxImporter(Connection conn) throws Exception {
        super(conn);
    }

    public ImportResult importData(File sourceFile){
        int count = 0;
        int recordCount = 0;
        long elapsedTime = 0;
        ImportResult importResult = null;
        PreparedStatement ps = null;
        FileInputStream fis = null;
        String filename = null;
        String sheetName = null;
        String tableName = null;

        try {
            filename = sourceFile.getName();
            fis = new FileInputStream(sourceFile);
            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = null;

            for (int sheetIndex = 0; ;sheetIndex++) {
                try {
                    sheet = workbook.getSheetAt(sheetIndex);
                    sheetName = sheet.getSheetName();
                    tableName = RegexUtil.getValidName(sheetName);
                } catch (Exception e) {
                    break;
                }
                Date startTime = new Date();
                System.out.println("  开始导入sheet:" + sheetName + "(表：" + tableName + ")...");
                XSSFRow titleRow = sheet.getRow(0);
                int columnNum = titleRow.getLastCellNum() - titleRow.getFirstCellNum(); // getFirstCellNum返回0,getLastCellNum()返回列数
                List<Column> columnList = new ArrayList<Column>();
                List<String> columnNameList = new ArrayList<String>();
                List<String> placeholderList = new ArrayList<String>();
                List<Integer> indexList = new ArrayList<Integer>();

                for (int i = titleRow.getFirstCellNum(); i < columnNum; i++) {
                    Cell columnCell = titleRow.getCell(i);
                    columnCell.setCellType(Cell.CELL_TYPE_STRING);
                    String cellName = columnCell.getStringCellValue();
                    if (!cellName.startsWith("@")) {
                        String columnName = RegexUtil.getValidName(cellName).toUpperCase();
                        if (columnName != null) {
                            String dataType = RegexUtil.getDataTypeStr(cellName);
                            String columnType = null;
                            String columnType2 = null;
                            if (dataType == null) {
                            	columnType = "VARCHAR2(4000)";
                                columnType2 = null;
                            } else if (dataType.toUpperCase().startsWith("V ")) {
                                columnType = "VARCHAR2(" + dataType.substring(2) + " char)";
                                columnType2 = null;
                            } else if (dataType.toUpperCase().startsWith("N ")) {
                            	columnType = "NUMBER(" + dataType.substring(2) + ")";
                                columnType2 = "NUMBER";
                            } else if (dataType.toUpperCase().startsWith("DATE ")) {
                                columnType = "DATE";
                                columnType2 = dataType;
                            }
                            System.out.println(columnName + "$" + columnType + "$" + columnType2);
                            Column column = new Column();
                            column.setName(columnName);
                            column.setType(columnType);
                            column.setType2(columnType2);
                            columnList.add(column);
                            columnNameList.add(columnName);
                            placeholderList.add("?");
                            indexList.add(i);
                        }
                    }
                }
                doBeofreImport(tableName, columnList);
                String insertStr = "insert into " + tableName + " (" + Joiner.on(',').join(columnNameList) + ") " + "values (" + Joiner.on(',').join(placeholderList) + ")";
                System.out.println("  导入语句：" + insertStr);
                ps = conn.prepareStatement(insertStr);
                /* getFirstRowNum()返回0,getLastRowNum()返回去掉标题行后的行数 */
                for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { 
                    if (null != sheet.getRow(rownum)) {
                        XSSFRow row = sheet.getRow(rownum); // 获得一个行
                        int paramIndex = 1;
                        for(Integer i : indexList){
                            Cell cell = row.getCell(i);
                            if (cell == null) {
                                ps.setObject(paramIndex++, null);
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                Column column = columnList.get(paramIndex - 1);
                                String columnType2 = column.getType2();
                                String value = cell.getStringCellValue().trim();
                                if (columnType2 == null) {
                                	ps.setString(paramIndex++, cell.getStringCellValue());
                                } else if (columnType2.equals("NUMBER")) {
                                    if (value == null || value.equals("")) {
                                        ps.setString(paramIndex++, null);
                                    } else {
                                        ps.setInt(paramIndex++, Integer.valueOf(value));
                                    }
                                } else if (columnType2.toUpperCase().startsWith("DATE")) {
                                	if (value == null || value.equals("")) {
                                        ps.setTimestamp(paramIndex++, null);
                                    } else {
                                        ps.setTimestamp(paramIndex++, new Timestamp(new SimpleDateFormat(columnType2.substring(5)).parse(value).getTime()));
                                    }
                                }
                            }
                        }
                        ps.addBatch();
                        count ++;
                        if (count == 10000) {
                            ps.executeBatch();
                            ps.clearBatch();
                            count = 0;
                            recordCount += 10000;
                            if (recordCount % 1000000 == 0) {
                                System.out.println("已插入" + recordCount + "条记录.");                    
                            }
                        }
                    }
                }
                ps.executeBatch();
                ps.clearBatch();
                recordCount += count;
                doAfterImport(tableName, filename, sheetName);
                System.out.println("  导入完成.记录数：" + recordCount);
                System.out.println(String.format("%-100s", "-").replace(' ', '-'));
                recordCount = 0;
                count = 0;

                Date endTime = new Date();
                elapsedTime = endTime.getTime() - startTime.getTime();
            }
            importResult = new ImportResult(true, recordCount, elapsedTime);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("  导入失败");
            importResult = new ImportResult(false);
        } finally {
            DbUtils.closeQuietly(ps);
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return importResult;
    }
	
}